pg-dump problem

Preface:
=======

Well, finally, we have decided to start backing up the Postgres databases
we have. And the decision even came before anything got destroyed...

So, our sysadmin basically made a modified version of the dumpall script,
that creates individual files for each database, which include the
day-of-week in their name. The effect is to have seven days worth of backup
on disk. Since the disk is backed up on tape anyway, it more-or-less covers
all.

Since this is based on dumpall, the files are, of course, merely pg_dump
outputs, no bells and whistles.

The postgres version is 6.2.1

The problem:
===========

Well, as we all know, when one creates a backup scheme, one must try to
restore from backup, to make sure that when shit happens, the backup will
not turn out to be useless.

So I tried to restore from one of those pg_dump outputs into a newly
created database.

It failed. Output includes several occurences of the explanation of
backslash commands, courtesy of psql. Some - but not all - of the tables
were restored correctly.

I think I traced down the problem to the fact that I have default values
which are SQL functions. Pg_dump dumps the SQL functions AFTER it dumps the
tables. So, when a table is defined, and the function on which it is based
does not exist, the creation fails. Then, the copy into that table fails,
and then, PSQL tries to interpret the input for that COPY as commands...

What can I do about it? I guess it is a "damn if you do, damn if you
don't", because some functions require the existence of tables, and some
tables require the existence of functions... But what am I to do about my
backups?

Rant:
====

It was quite difficult to trace the origin of the problem, because when one
uses psql with redirected input, one does not get a visual feedback in the
same way as one gets when using -f. On the other hand, one cannot use -f
with pg_dump files, because it waits for the COPY data on the standard
input - not on the file of origin.

Before you suggest using pg_dump with proper inserts rather than COPY -
there is a bug in psql, which causes some of the text fields to be
truncated when using proper inserts. At least, when I'm using 8-bit text.
So it's not an option.

If any of the above has been fixed in 6.3, let me know. The problem is that
if I don't have a reliable way of restoring the data when we want to
upgrade, our sysadmin won't upgrade - and rightly so.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma